Excel-Based Evaluation Solutions
MS Excel + VBA macros
Icon

Portfolio Description

This portfolio piece showcases a custom evaluation system that I originally developed for use in a professional setting. This version represents a refined and streamlined iteration of that original system, enhanced with VBA macro automation for improved efficiency. All names and identifying details have been replaced with randomly generated data to ensure confidentiality. A demonstration video showcasing the system's functionality is embedded below. To request the full Excel file, click the 'Request portfolio file' button at the bottom right corner.

Data Lookup and Filtering Module

This section of the evaluation system focuses on efficient data retrieval and preparation for the evaluation process. The "Evaluation Data Lookup" sheet provides a powerful interface for querying and filtering relevant interaction data. Key features include:

  • Dynamic Data Filtering: A robust FILTER formula enables precise data selection based on multiple criteria, including date ranges, agent identifiers, fault types, escalation points, and SLA adherence. This eliminates manual searching and significantly improves efficiency.
  • Automated Data Transfer: A VBA macro ("Evaluate selected Interaction ID") automates the transfer of selected interaction data to an evaluation form, preventing manual data entry errors and saving valuable time. This macro also includes a duplicate check to prevent re-evaluation of already evaluated Interaction IDs.
  • Default Query Reset: A "Reset Query to Default" button, powered by a VBA macro, quickly resets the filter criteria to a predefined default, providing a convenient way to start a new search.
  • Error Handling and User Feedback: The system includes robust error handling with informative message boxes to guide users and prevent unexpected behavior.

This module plays a crucial role in the overall evaluation workflow by providing a streamlined and efficient method for accessing and preparing data for evaluation. It integrates seamlessly with other components of the system (which will be described in subsequent sections).

Dynamic, Context-Sensitive Evaluation Module

This module provides a dynamic and context-sensitive evaluation form, designed to streamline the evaluation process. It integrates seamlessly with the "Data Lookup and Filtering Module" and offers key features like:

  • Contextual Questioning: Interconnected IFS formulas dynamically display relevant evaluation questions based on previous responses. This ensures that only pertinent questions are presented, maintaining focus and improving the efficiency of the evaluation process. The questions are derived from the "CCV Process Map" sheet, a pre-defined evaluation framework and the primary source for the evaluation questions.
  • Automated Evaluation ID Generation: A "Generate" button utilizes a VBA macro to automatically create a unique evaluation ID based on the employee ID, current date, and time. This ensures proper tracking and avoids manual ID creation. The ID is updated if the Employee ID is changed after the ID is generated.
  • Automated Data Population: Data from the "Evaluation Data Lookup" sheet is automatically populated into relevant fields on this form, minimizing manual data entry and reducing the risk of errors.
  • Evaluation Submission and Logging: A "Submit Evaluation" button triggers a VBA macro that performs several actions:
    • Automated Evaluation ID Generation (Fallback): If an evaluation ID has not already been generated using the "Generate" button, the submission process automatically generates one to ensure every evaluation is properly identified.
    • The system creates a new sheet containing a copy of the completed evaluation.
    • The system logs the evaluation details in the "Evaluation Logs" sheet, including a hyperlink to the newly created evaluation sheet.
    • The system clears the current evaluation form for the next evaluation
    • The system includes robust validation to ensure the form is fully filled out before submission.
  • Form Reset: A "Clear Evaluation" button provides a quick way to reset the form and clear all entered data.

This module is a crucial component of the overall evaluation system, providing a user-friendly and efficient interface for conducting and recording evaluations. It integrates seamlessly with the "Data Lookup and Filtering Module" and the "Evaluation Logs" sheet (described in other sections).

Evaluation Log and Management Module

This module provides a centralized repository for all completed evaluations and offers tools for managing these records. Key features include:

  • Centralized Evaluation Logging: This sheet stores a summary of each submitted evaluation, including timestamps, employee information, interaction details, evaluation scores, and a unique Evaluation ID. This provides a comprehensive overview of all evaluations conducted.
  • Hyperlinked Evaluation Details: Each logged evaluation includes a hyperlink ("Details") that directly links to the corresponding evaluation sheet, allowing for quick and easy access to the full evaluation details.
  • Automated Record Deletion and Sheet Removal: Each logged evaluation includes a hyperlink ("Details") that directly links to the corresponding evaluation sheet, allowing for quick and easy access to the full evaluation details.
  • Data Integrity and Validation: The deletion macro includes several checks:
    • It only allows deletion of cells within the designated Evaluation ID column (M2:M2001).
    • It checks if the selected cells are not blank before proceeding with the deletion.
    • It provides a confirmation message before deleting any data.

This module is essential for maintaining a clear and organized record of all evaluations. It integrates seamlessly with the "Evaluation Form" module, providing a complete solution for evaluation management.